CodingPro

1. Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. They are commonly used with the GROUP BY clause in SQL.

a. COUNT()

Returns the number of rows that match a specified condition.

SELECT COUNT(*) FROM customers;
SELECT COUNT(customer_id) FROM orders WHERE status = 'completed';

b. SUM()

Calculates the total sum of a numeric column.

SELECT SUM(price) FROM products;
SELECT SUM(salary) FROM employees WHERE department = 'Sales';

c. AVG()

Computes the average value of a numeric column.

SELECT AVG(price) FROM products;
SELECT AVG(salary) FROM employees WHERE department = 'HR';

d. MIN()

Finds the smallest value in a column.

SELECT MIN(price) FROM products;
SELECT MIN(salary) FROM employees WHERE department = 'IT';

e. MAX()

Finds the largest value in a column.

SELECT MAX(price) FROM products;
SELECT MAX(salary) FROM employees WHERE department = 'Finance';

2. String Functions

String functions manipulate and transform string data.

a. UPPER()

Converts all characters to uppercase.

SELECT UPPER(name) FROM customers;

b. LOWER()

Converts all characters to lowercase.

SELECT LOWER(email) FROM users;

c. CONCAT()

Joins two or more strings together.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

d. SUBSTRING()

Extracts a portion of a string.

SELECT SUBSTRING(name, 1, 5) FROM products;

3. Date and Time Functions

Date and time functions manipulate date/time values in SQL.

a. CURRENT_DATE()

Returns the current date.

SELECT CURRENT_DATE;

b. CURRENT_TIME()

Returns the current time.

SELECT CURRENT_TIME;

c. NOW()

Returns the current date and time.

SELECT NOW();

d. DATEADD()

Adds a specified number of days, months, or years to a date.

SELECT DATEADD(DAY, 5, '2025-02-09');

e. DATEDIFF()

Calculates the difference between two dates.

SELECT DATEDIFF('2025-02-09', '2025-01-01');

4. Mathematical Functions

Mathematical functions perform numeric calculations.

a. ABS()

Returns the absolute value of a number.

SELECT ABS(-10);

b. CEIL()

Rounds a number up to the nearest integer.

SELECT CEIL(4.2);

c. FLOOR()

Rounds a number down to the nearest integer.

SELECT FLOOR(4.9);

d. ROUND()

Rounds a number to a specified number of decimal places.

SELECT ROUND(4.678, 2);

e. POWER()

Returns a number raised to a specified power.

SELECT POWER(2, 3);

These SQL functions are essential for working with data efficiently in relational databases. Mastering them will help in data manipulation and analysis.